{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def import_data_from_excel(file_path):\n",
    "    # 读取 Excel 文件\n",
    "    data = pd.read_excel(file_path, engine='openpyxl')  # 使用 openpyxl 引擎\n",
    "    \n",
    "    # 初始化存储结构\n",
    "    students = {}\n",
    "    \n",
    "    # 遍历每行数据，存入字典\n",
    "    for _, row in data.iterrows():\n",
    "        student_id = str(row['ID'])  # 学生ID\n",
    "        students[student_id] = {\n",
    "            \"name\": row['姓名'],  # 学生姓名\n",
    "            \"scores\": row[2:-1].tolist(),  # 各题得分（假设题目列在第3列到倒数第2列）\n",
    "            \"total\": row['总分']  # 总分\n",
    "        }\n",
    "    \n",
    "    return students\n",
    "\n",
    "# 导入数据\n",
    "file_path = r\"\"C:\\Users\\pan\\Desktop\\新建 XLS 工作表.xls\"\" \n",
    "students_data = import_data_from_excel(file_path)\n",
    "\n",
    "print(students_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "class GradeAnalysis:\n",
    "    def __init__(self, file_path):\n",
    "        # 导入数据\n",
    "        self.data = pd.read_excel(file_path, engine='openpyxl')\n",
    "        self.students = self._process_data()\n",
    "\n",
    "    def _process_data(self):\n",
    "        \"\"\"\n",
    "        处理 Excel 数据并存储为字典格式\n",
    "        \"\"\"\n",
    "        students = {}\n",
    "        for _, row in self.data.iterrows():\n",
    "            student_id = str(row['ID'])  # 学生ID\n",
    "            students[student_id] = {\n",
    "                \"name\": row['姓名'],  # 学生姓名\n",
    "                \"scores\": row[2:-1].tolist(),  # 各题得分（假设题目列在第3列到倒数第2列）\n",
    "                \"total\": row['总分']  # 总分\n",
    "            }\n",
    "        return students\n",
    "\n",
    "    def grade_distribution(self):\n",
    "        \"\"\"\n",
    "        统计成绩分布：成绩区间人数\n",
    "        \"\"\"\n",
    "        bins = [0, 60, 70, 80, 90, 100]  # 分数区间\n",
    "        labels = ['不及格', '及格', '中等', '良好', '优秀']\n",
    "        self.data['分数区间'] = pd.cut(self.data['总分'], bins=bins, labels=labels, right=False)\n",
    "        distribution = self.data['分数区间'].value_counts().sort_index()\n",
    "        return distribution\n",
    "\n",
    "    def pass_rate_and_average(self):\n",
    "        \"\"\"\n",
    "        统计及格率和平均分\n",
    "        \"\"\"\n",
    "        total_students = len(self.data)\n",
    "        passed_students = len(self.data[self.data['总分'] >= 60])\n",
    "        pass_rate = passed_students / total_students * 100\n",
    "        average_score = self.data['总分'].mean()\n",
    "        return pass_rate, average_score\n",
    "\n",
    "    def single_question_analysis(self):\n",
    "        \"\"\"\n",
    "        单题得分分析：统计每道题的平均分、最高分、最低分\n",
    "        \"\"\"\n",
    "        question_columns = self.data.columns[2:-1]  # 假设题目列在第3列到倒数第2列\n",
    "        analysis = {}\n",
    "        for question in question_columns:\n",
    "            average_score = self.data[question].mean()\n",
    "            max_score = self.data[question].max()\n",
    "            min_score = self.data[question].min()\n",
    "            analysis[question] = {\n",
    "                \"average\": average_score,\n",
    "                \"max\": max_score,\n",
    "                \"min\": min_score\n",
    "            }\n",
    "        return analysis\n",
    "\n",
    "    def rank_students(self):\n",
    "        \"\"\"\n",
    "        按总分排名，并分类统计\n",
    "        \"\"\"\n",
    "        self.data['排名'] = self.data['总分'].rank(ascending=False, method='min')  # 按总分排名\n",
    "        bins = [0, 60, 70, 80, 90, 100]  # 分数区间\n",
    "        labels = ['不及格', '及格', '中等', '良好', '优秀']\n",
    "        self.data['分类'] = pd.cut(self.data['总分'], bins=bins, labels=labels, right=False)\n",
    "        rank_data = self.data.sort_values(by='总分', ascending=False)[['ID', '姓名', '总分', '排名', '分类']]\n",
    "        return rank_data\n",
    "\n",
    "# 示例：使用数据分析功能\n",
    "file_path = r\"D:\\other\\1st\\shuju.xlsx\"  # 替换为实际 Excel 文件路径\n",
    "analysis = GradeAnalysis(file_path)\n",
    "\n",
    "# 1. 成绩分布统计\n",
    "print(\"成绩分布统计：\")\n",
    "print(analysis.grade_distribution())\n",
    "\n",
    "# 2. 统计及格率和平均分\n",
    "pass_rate, average_score = analysis.pass_rate_and_average()\n",
    "print(f\"\\n及格率：{pass_rate:.2f}%\")\n",
    "print(f\"平均分：{average_score:.2f}\")\n",
    "\n",
    "# 3. 单题得分分析\n",
    "print(\"\\n单题得分分析：\")\n",
    "single_question_stats = analysis.single_question_analysis()\n",
    "for question, stats in single_question_stats.items():\n",
    "    print(f\"{question} - 平均分: {stats['average']:.2f}, 最高分: {stats['max']}, 最低分: {stats['min']}\")\n",
    "\n",
    "# 4. 学生成绩排名\n",
    "print(\"\\n学生成绩排名：\")\n",
    "print(analysis.rank_students())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "\n",
    "# 数据导入函数\n",
    "def import_data(file_path):\n",
    "    if file_path.endswith('.csv'):\n",
    "        data = pd.read_csv(file_path)\n",
    "    elif file_path.endswith('.xlsx'):\n",
    "        data = pd.read_excel(file_path)\n",
    "    else:\n",
    "        raise ValueError(\"不支持的文件格式\")\n",
    "    return data\n",
    "\n",
    "\n",
    "# 成绩分析类\n",
    "class GradeAnalyzer:\n",
    "    def __init__(self, data):\n",
    "        self.data = data\n",
    "        self.student_grades = {}\n",
    "        for index, row in data.iterrows():\n",
    "            self.student_grades[row['学生ID']] = {\n",
    "                '各题得分': row[['题1得分', '题2得分', '题3得分',...]].to_dict(),  # 根据实际题目列名修改\n",
    "                '总分': row['总分']\n",
    "            }\n",
    "\n",
    "    # 成绩分布统计\n",
    "    def grade_distribution(self):\n",
    "        grades = self.data['总分']\n",
    "        bins = [0, 60, 70, 80, 90, 100]\n",
    "        labels = ['不及格', '及格', '中', '良', '优']\n",
    "        grade_categories = pd.cut(grades, bins=bins, labels=labels, right=False)\n",
    "        distribution = grade_categories.value_counts().sort_index()\n",
    "        total_students = len(grades)\n",
    "        pass_rate = (grades >= 60).sum() / total_students\n",
    "        average_grade = grades.mean()\n",
    "\n",
    "        print(\"成绩分布情况：\")\n",
    "        for category, count in distribution.items():\n",
    "            print(f\"{category}: {count}人，占比：{count / total_students:.2%}\")\n",
    "        print(f\"及格率：{pass_rate:.2%}\")\n",
    "        print(f\"平均分：{average_grade:.2f}\")\n",
    "\n",
    "        # 绘制柱状图\n",
    "        distribution.plot(kind='bar')\n",
    "        plt.title('成绩分布')\n",
    "        plt.xlabel('成绩区间')\n",
    "        plt.ylabel('学生人数')\n",
    "        plt.show()\n",
    "\n",
    "    # 单题得分分析\n",
    "    def single_question_analysis(self):\n",
    "        question_scores = []\n",
    "        for question in ['题1得分', '题2得分', '题3得分',...]:  # 根据实际题目列名修改\n",
    "            scores = self.data[question]\n",
    "            average_score = scores.mean()\n",
    "            question_scores.append(average_score)\n",
    "            print(f\"{question}平均得分：{average_score:.2f}\")\n",
    "\n",
    "        # 绘制单题得分柱状图\n",
    "        plt.bar(['题1', '题2', '题3',...], question_scores)  # 根据实际题目列名修改\n",
    "        plt.title('单题得分情况')\n",
    "        plt.xlabel('题目')\n",
    "        plt.ylabel('平均得分')\n",
    "        plt.show()\n",
    "\n",
    "    # 学生成绩排名\n",
    "    def student_rank(self):\n",
    "        sorted_data = self.data.sort_values(by='总分', ascending=False)\n",
    "        print(\"学生成绩排名：\")\n",
    "        print(sorted_data[['学生ID', '姓名', '总分']])\n",
    "\n",
    "\n",
    "# 示例用法\n",
    "file_path = 'your_file.csv'  # 替换为实际文件路径\n",
    "data = import_data(file_path)\n",
    "analyzer = GradeAnalyzer(data)\n",
    "analyzer.grade_distribution()\n",
    "analyzer.single_question_analysis()\n",
    "analyzer.student_rank()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 绘制成绩分布柱状图\n",
    "distribution.plot(kind='bar')\n",
    "plt.title('成绩分布')\n",
    "plt.xlabel('成绩区间')\n",
    "plt.ylabel('学生人数')\n",
    "plt.show()\n",
    "\n",
    "# 绘制单题得分柱状图\n",
    "plt.bar(['题1', '题2', '题3',...], question_scores)\n",
    "plt.title('单题得分情况')\n",
    "plt.xlabel('题目')\n",
    "plt.ylabel('平均得分')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "\n",
    "# 数据导入函数\n",
    "def import_data(file_path):\n",
    "    if file_path.endswith('.csv'):\n",
    "        data = pd.read_csv(file_path)\n",
    "    elif file_path.endswith('.xlsx'):\n",
    "        data = pd.read_excel(file_path)\n",
    "    else:\n",
    "        raise ValueError(\"不支持的文件格式\")\n",
    "    return data\n",
    "\n",
    "\n",
    "# 成绩分析类\n",
    "class GradeAnalyzer:\n",
    "    def __init__(self, data):\n",
    "        self.data = data\n",
    "        self.student_grades = {}\n",
    "        for index, row in data.iterrows():\n",
    "            self.student_grades[row['学生ID']] = {\n",
    "                '各题得分': row[['题1得分', '题2得分', '题3得分',...]].to_dict(),  # 根据实际题目列名修改\n",
    "                '总分': row['总分']\n",
    "            }\n",
    "\n",
    "    # 成绩分布统计\n",
    "    def grade_distribution(self):\n",
    "        grades = self.data['总分']\n",
    "        bins = [0, 60, 70, 80, 90, 100]\n",
    "        labels = ['不及格', '及格', '中', '良', '优']\n",
    "        grade_categories = pd.cut(grades, bins=bins, labels=labels, right=False)\n",
    "        distribution = grade_categories.value_counts().sort_index()\n",
    "        total_students = len(grades)\n",
    "        pass_rate = (grades >= 60).sum() / total_students\n",
    "        average_grade = grades.mean()\n",
    "\n",
    "        print(\"成绩分布情况：\")\n",
    "        for category, count in distribution.items():\n",
    "            print(f\"{category}: {count}人，占比：{count / total_students:.2%}\")\n",
    "        print(f\"及格率：{pass_rate:.2%}\")\n",
    "        print(f\"平均分：{average_grade:.2f}\")\n",
    "\n",
    "        # 绘制柱状图\n",
    "        distribution.plot(kind='bar')\n",
    "        plt.title('成绩分布')\n",
    "        plt.xlabel('成绩区间')\n",
    "        plt.ylabel('学生人数')\n",
    "        plt.show()\n",
    "\n",
    "    # 单题得分分析\n",
    "    def single_question_analysis(self):\n",
    "        question_scores = []\n",
    "        for question in ['题1得分', '题2得分', '题3得分',...]:  # 根据实际题目列名修改\n",
    "            scores = self.data[question]\n",
    "            average_score = scores.mean()\n",
    "            question_scores.append(average_score)\n",
    "            print(f\"{question}平均得分：{average_score:.2f}\")\n",
    "\n",
    "        # 绘制单题得分柱状图\n",
    "        plt.bar(['题1', '题2', '题3',...], question_scores)  # 根据实际题目列名修改\n",
    "        plt.title('单题得分情况')\n",
    "        plt.xlabel('题目')\n",
    "        plt.ylabel('平均得分')\n",
    "        plt.show()\n",
    "\n",
    "    # 学生成绩排名\n",
    "    def student_rank(self):\n",
    "        sorted_data = self.data.sort_values(by='总分', ascending=False)\n",
    "        print(\"学生成绩排名：\")\n",
    "        print(sorted_data[['学生ID', '姓名', '总分']])\n",
    "\n",
    "    # 学生个性化反馈报告生成\n",
    "    def generate_student_feedback(self, student_id):\n",
    "        student_info = self.student_grades[student_id]\n",
    "        feedback = f\"学生 {student_id} 的成绩分析报告：\\n\"\n",
    "        feedback += f\"总分：{student_info['总分']}\\n\"\n",
    "        for question, score in student_info['各题得分'].items():\n",
    "            feedback += f\"{question}：得分 {score}\\n\"\n",
    "            if score < 60:  # 简单判断为错题，可根据实际情况调整\n",
    "                feedback += f\"  本题回答错误，建议复习相关知识点。\\n\"\n",
    "        return feedback\n",
    "\n",
    "    # 教学改进建议生成\n",
    "    def generate_teaching_improvement(self):\n",
    "        question_scores = []\n",
    "        for question in ['题1得分', '题2得分', '题3得分',...]:  # 根据实际题目列名修改\n",
    "            scores = self.data[question]\n",
    "            average_score = scores.mean()\n",
    "            question_scores.append(average_score)\n",
    "\n",
    "        improvement_suggestions = []\n",
    "        for i, score in enumerate(question_scores):\n",
    "            if score < 60:  # 假设平均得分低于 60 分的题目需要改进教学\n",
    "                improvement_suggestions.append(f\"题目 {i + 1} 学生整体得分较低，建议加强该知识点的教学。\")\n",
    "\n",
    "        return improvement_suggestions\n",
    "\n",
    "\n",
    "# 示例用法\n",
    "file_path = 'your_file.csv'  # 替换为实际文件路径\n",
    "data = import_data(file_path)\n",
    "analyzer = GradeAnalyzer(data)\n",
    "analyzer.grade_distribution()\n",
    "analyzer.single_question_analysis()\n",
    "analyzer.student_rank()\n",
    "\n",
    "# 生成某个学生的个性化反馈报告\n",
    "student_id = '001'  # 替换为实际学生 ID\n",
    "print(analyzer.generate_student_feedback(student_id))\n",
    "\n",
    "# 生成教学改进建议\n",
    "print(analyzer.generate_teaching_improvement())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
